import openpyxl
from openpyxl.drawing.image import Image
from openpyxl.drawing.xdr import XDRPositiveSize2D
from openpyxl.drawing.spreadsheet_drawing import OneCellAnchor, AnchorMarker
from openpyxl.utils.units import pixels_to_EMU
from openpyxl.utils import get_column_letter
from PIL import Image as PilImage
from PIL import ImageOps


def addImageToCell(ws, cell_coord, img_path):
    """
    该函数封装了openpyxl里将图片插入到单元格并单元格（基本）居中的方法，使用该函数需要确保openpyxl==3.1.5和pillow==10.4.0的正确安装
    :param ws: openpyxl里的WorkSheet实例
    :param cell_coord: 包含了单元格坐标的元组，(row: start from 1, col: start from 1)
    :param img_path: 原图片存放路径
    :return: None，图片插入到指定位置后返回None，WorkBook并未保存
    """
    # 获取目标单元格
    cell = ws.cell(row=cell_coord[0], column=cell_coord[1])
    col = cell.column
    row = cell.row

    # 转换单位到像素
    # 列宽转换（1字符宽度 ≈ 7像素）
    col_width = ws.column_dimensions[get_column_letter(col)].width
    cell_width_px = col_width * 7

    # 行高转换（1点 ≈ 1.333像素）
    row_height = ws.row_dimensions[row].height
    cell_height_px = row_height * 1.333 if row_height else 15 * 1.333

    # 用Pillow处理图片
    PilImage.MAX_IMAGE_PIXELS = None  # 关闭限制
    pil_img = PilImage.open(img_path)

    # 调整图像角度
    exif = pil_img.getexif()
    
    # 获取Orientation标签的值（默认1，即正常方向）
    orientation = exif.get(274, 1)
    
    # 根据不同的Orientation值处理图像
    if orientation == 2:
        pil_img = ImageOps.mirror(pil_img)
    elif orientation == 3:
        pil_img = pil_img.rotate(180, expand=True)
    elif orientation == 4:
        pil_img = ImageOps.flip(pil_img)
    elif orientation == 5:
       pil_img = ImageOps.mirror(pil_img).rotate(-90, expand=True)
    elif orientation == 6:
        pil_img = pil_img.rotate(-90, expand=True)
    elif orientation == 7:
        pil_img = ImageOps.mirror(pil_img).rotate(90, expand=True)
    elif orientation == 8:
        pil_img = pil_img.rotate(90, expand=True)


    img_w, img_h = pil_img.size

    # 计算等比缩放比例
    scale = min(cell_width_px / img_w, cell_height_px / img_h)
    new_width = int(img_w * scale)
    new_height = int(img_h * scale)
    # 验证缩放尺寸
    if new_width <= 0 or new_height <= 0:
        raise ValueError(f"无效的缩放尺寸：{new_width}x{new_height}")
    pil_img = pil_img.resize((new_width, new_height), PilImage.Resampling.LANCZOS)

    # 内存保存图片（避免临时文件）
    from io import BytesIO
    img_stream = BytesIO()
    pil_img.save(img_stream, format='PNG')
    img_stream.seek(0)
    # 创建openpyxl图片对象
    img = Image(img_stream)
    size = XDRPositiveSize2D(pixels_to_EMU(new_width), pixels_to_EMU(new_height))

    # 计算居中偏移量（单位：EMU）
    offset_x = (cell_width_px - new_width) / 2  # 水平居中偏移
    offset_y = (cell_height_px - new_height) / 2  # 垂直居中偏移

    # 创建锚点定位对象
    anchor = OneCellAnchor(
        _from=AnchorMarker(
            col=col - 1,  # 列索引转0基
            row=row - 1,  # 行索引转0基
            colOff=pixels_to_EMU(offset_x),  # 水平偏移
            rowOff=pixels_to_EMU(offset_y)  # 垂直偏移
        ),
        ext=size
    )
    img.anchor = anchor

    # 添加图片到工作表
    ws.add_image(img)


if __name__ == '__main__':
    # 使用示例
    wb = openpyxl.Workbook()
    ws = wb.active

    # 调整目标单元格尺寸（演示用）
    ws.column_dimensions['B'].width = 20  # 字符宽20约140像素
    ws.row_dimensions[3].height = 40  # 点40约53.32像素

    # 调用函数插入图片
    addImageToCell(ws, (2, 3), './20250218_IMG/20250218.jpg')

    wb.save('output.xlsx')

